package com.zhaoyang.dao;

import com.zhaoyang.model.Page;
import com.zhaoyang.model.Score;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 赵扬
 * @time 2020/4/27 19:10
 */
public class ScoreDao extends BaseDao {
    /**
     * 成绩录入
     *
     * @param score 成绩信息实体
     * @return 是否录入成功
     */
    public boolean addScore(Score score) {
        String sql = "insert into s_score values(null,?,?,?,?)";

        int cnt = template.update(sql, score.getStudentId(),
                score.getCourseId(), score.getScore(), score.getRemark());
        return cnt == 1;
    }

    /**
     * 删除成绩
     *
     * @param id 成绩信息编号
     * @return 是否成功删除
     */
    public boolean deleteScore(int id) {
        String sql = "delete from s_score where id = ?";
        int cnt = template.update(sql, id);
        return cnt == 1;
    }

    /**
     * 修改成绩
     *
     * @param score 成绩信息实体
     * @return 是否修改成功
     */
    public boolean editScore(Score score) {

        String sql = "update s_score set student_id = ?,course_id = ?,score = ?" +
                ",remark = ? where id = ?";
        int cnt = template.update(sql, score.getStudentId(), score.getCourseId(), score.getScore(),
                score.getRemark(), score.getId());
        return cnt == 1;
    }

    /**
     * 判断成绩是否录入
     *
     * @param studentId 学生编号
     * @param courseId  课程编号
     * @return 是否录入成绩
     */
    public boolean isAdd(int studentId, int courseId) {
        String sql = "select * from s_score where student_id = ? and course_id = ?";
        List<Score> scores = template.query(sql, new BeanPropertyRowMapper<Score>(Score.class),
                studentId, courseId);
        if (0 == scores.size()) {
            return false;
        }
        return true;
    }

    /**
     * 分页获取成绩列表
     *
     * @param score 成绩信息
     * @param page  分页信息
     * @return 成绩列表
     */
    public List<Score> getScoreList(Score score, Page page) {
        List<Score> ret = new ArrayList<Score>();
        String sql = "select * from s_score ";
        if (score.getStudentId() != 0) {
            sql += " and student_id = " + score.getStudentId();
        }
        if (score.getCourseId() != 0) {
            sql += " and course_id = " + score.getCourseId();
        }
        sql += " limit " + page.getStart() + "," + page.getPageSize();
        sql = sql.replaceFirst("and", "where");
        ResultSet resultSet = query(sql);
        try {
            while (resultSet.next()) {
                Score s = new Score();
                s.setId(resultSet.getInt("id"));
                s.setCourseId(resultSet.getInt("course_id"));
                s.setStudentId(resultSet.getInt("student_id"));
                s.setScore(resultSet.getDouble("score"));
                s.setRemark(resultSet.getString("remark"));
                ret.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 获取符合某一条件的所有成绩列表
     *
     * @param score
     * @return
     */
    public List<Map<String, Object>> getScoreList(Score score) {
        List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
        String sql = "select s_score.*,s_student.name as studentName,s_course.name as courseName from s_score,s_student,s_course where s_score.student_id=s_student.id and s_score.course_id=s_course.id ";
        if (score.getStudentId() != 0) {
            sql += "and student_id = " + score.getStudentId();
        }
        if (score.getCourseId() != 0) {
            sql += "and course_id = " + score.getCourseId();
        }
        ResultSet resultSet = query(sql);
        try {
            while (resultSet.next()) {
                Map<String, Object> s = new HashMap<String, Object>();
                s.put("id", resultSet.getInt("id"));
                s.put("courseId", resultSet.getInt("course_id"));
                s.put("studentId", resultSet.getInt("student_id"));
                s.put("score", resultSet.getDouble("score"));
                s.put("remark", resultSet.getString("remark"));
                s.put("studentName", resultSet.getString("studentName"));
                s.put("courseName", resultSet.getString("courseName"));
                ret.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }


    /**
     * 获取成绩总记录数
     *
     * @param score
     * @return
     */
    public int getScoreListTotal(Score score) {
        int total = 0;
        String sql = "select count(*) as total from s_score ";
        if (score.getStudentId() != 0) {
            sql += " and student_id = " + score.getStudentId();
        }
        if (score.getCourseId() != 0) {
            sql += " and course_id = " + score.getCourseId();
        }
        sql = sql.replaceFirst("and", "where");
        ResultSet resultSet = query(sql);
        try {
            while (resultSet.next()) {
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }


    /**
     * 计算每个课程的学生平均成绩
     *
     * @param courseId 课程编号
     * @return map
     */
    // TODO >> 修改: 形参修改为courseId; 方法签名修改为getAvgStatsById
    public Map<String, Object> getAvgStatsById(int courseId) {
        String sql = "select max(t1.score) as max_score,avg(t1.score) as avg_score," +
                "min(t1.score) as min_score,t2.name as courseName " +
                "from s_score t1,s_course t2 where t1.course_id=t2.id " +
                "and t1.course_id = ?";
        Map<String, Object> map = template.queryForMap(sql, courseId);
        return map;
    }

}
